import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SparkSession

object ab {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .master("local[*]")
      .appName("app")
      .config("hive.metastore.uris", "thrift://cdh02:9083")
      .enableHiveSupport()
      .getOrCreate()


    spark.sql("use zg6_stock")
    spark.sql("select * from zg6_stock.apple_stock_trade").show(10)

//编写SQL查询苹果股票交易数据，统计数据量和展示样本数据
    spark.sql("select * from zg6_stock.apple_stock_trade").show()

//计算每天苹果股票最高价与最低价的差值，找出波动幅度最大的前 10 天降序排序
    spark.sql("select date1,(high-low) as cz from zg6_stock.apple_stock_trade order by cz desc limit 10").show()

//假设每股收益（EPS）为固定值 5 美元，计算每天的市盈率（PE 比率 = 收盘价 / 每股收益），并找出市盈率最高的前 5 天
  spark.sql("select date1,nvl(close,0)/5 as price from zg6_stock.apple_stock_trade order by price desc limit 5").show()

//计算每年的平均开盘价
    spark.sql(" select year(date1) as nian,avg(open) as pj from zg6_stock.apple_stock_trade group by nian"
      ).show()
    //统计每天最高股票于最低股票差值，找出相差最大前20.
    spark.sql("select date1,(high-low) as cz from zg6_stock.apple_stock_trade order by cz desc limit 20").show()
    //统计每月的总成交量
    spark.sql("select month(date1) as yue,sum(volume) as z_num from zg6_stock.apple_stock_trade group by yue").show()
    //统计每个月的平均开盘价和平均收盘要求统计每个月苹果股票的平均开盘价和平均收盘价，并按照月份升序排序
    spark.sql("select month(date1) as yue,avg(open) as pj1,avg(close) as pj2 from zg6_stock.apple_stock_trade group by yue order by yue ").show()
    //找出交易量超过当月平均交易量的日期需要找出每个月中，交易量超过该月平均交易量
    spark.sql("select month(year) as yue,volume,avg(volume) as pj from zg6_stock.apple_stock_trade where volume>avg(volume) group by yue order by yue ").show()
    spark.stop()
  }
}
